IF OBJECT_ID('dbo.GetSecondarySponsors') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetSecondarySponsors
    IF OBJECT_ID('dbo.GetSecondarySponsors') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetSecondarySponsors >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetSecondarySponsors >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetSecondarySponsors
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/24/2007
--
-- Description: Returns list of secondary Sponsors from UDF table
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetSecondarySponsors()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/20/07     KC        Created
-- 8/15/07     KC        Permissions
-- 8/23/07     KC        Changed to new location for Secondary Sponsors
-- 11/18/09	AAB		 added InfoEd OriginatingSponCode
-- =======================================================================
CREATE PROCEDURE dbo.GetSecondarySponsors
     (@Name                        VARCHAR(50)=NULL,
     @DomainUserId                 INT
     )
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     Name           VARCHAR             
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT
               
     -- Result Query    
     SELECT DISTINCT ASP.Id AS SponsorId, 
	ASP.InfoEdSponCode as OriginatingSponCode,--added by aab 11/18/09
	 ASP.[Name] AS Name --aab 7/30/2009 replaced ASP.ShortName AS Name
     FROM      InfoEdProposal IEP JOIN dbo.AdmSponsor ASP ON IEP.orig_spon = ASP.InfoEdSponCode
     WHERE     IEP.orig_spon IS NOT NULL AND
    ((ASP.[Name] LIKE '%' + @Name + '%') OR @Name IS NULL)--aab 7/30/2009 replaced ASP.ShortName
    ORDER BY  ASP.[Name] --aab 7/30/2009 replaced ASP.ShortName
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetSecondarySponsors') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetSecondarySponsors >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetSecondarySponsors >>>'
go

GRANT EXECUTE ON dbo.GetSecondarySponsors TO InsightReport,Insight
go
